package com.webwork.lims.transport.dao.impl;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Repository;

import com.webwork.lims.common.dao.impl.SimpleDao;
import com.webwork.lims.transport.dao.ZoneDao;
import com.webwork.lims.transport.dataobject.Zone;
import com.webwork.lims.util.ColumnLimit;
import com.webwork.lims.util.DBSimpleSQL;
import com.webwork.lims.util.StrManager;

@Repository
public class ZoneDaoImpl extends SimpleDao implements ZoneDao {

	public ZoneDaoImpl() {
		super();
		this.dbTable = "t_zone";
		dbColumns.clear();
		dbColumns.put("zoneId", "ZONE_ID");
		dbColumns.put("country", "COUNTRY");
		dbColumns.put("province", "PROVINCE");
		dbColumns.put("city", "CITY");
		dbColumns.put("delivery", "DELIVERY");
	}
	
	@Override
	public ColumnLimit condition(Object object) {
		if (!(object instanceof Zone))
			return new ColumnLimit();
		Zone o = (Zone) object;
		ColumnLimit ret = new ColumnLimit();
		if (o.getZoneCode() != 0) {
			ret.add(dbColumns.get("zoneId") + " = ");
		}
		if (!StrManager.isEmpty(o.getCountry())) {
			ret.add(dbColumns.get("country") + " = ");
		}
		if (!StrManager.isEmpty(o.getProvince())) {
			ret.add(dbColumns.get("province") + " = ");
		}
		if (!StrManager.isEmpty(o.getCity())) {
			ret.add(dbColumns.get("city") + " = ");
		}
		if (!StrManager.isEmpty(o.getDelivery())) {
			ret.add(dbColumns.get("delivery") + " = ");
		}
		return ret;
	}

	@Override
	public Object[] statement(Object object) {
		Zone o = (Zone) object;
		List<Object> ret = new ArrayList<Object>();
		if (o.getZoneCode() != 0) {
			ret.add(o.getZoneCode());
		}
		if (!StrManager.isEmpty(o.getCountry())) {
			ret.add(o.getCountry());
		}
		if (!StrManager.isEmpty(o.getProvince())) {
			ret.add(o.getProvince());
		}
		if (!StrManager.isEmpty(o.getCity())) {
			ret.add(o.getCity());
		}
		if (!StrManager.isEmpty(o.getDelivery())) {
			ret.add(o.getDelivery());
		}
		return ret.toArray();
	}

	@Override
	public List<Object> pushObject(Object object) {
		Zone o = (Zone) object;
		List<Object> objs = new ArrayList<Object>();
		objs.add(o.getZoneCode());
		objs.add(o.getCountry());
		objs.add(o.getProvince());
		objs.add(o.getCity());
		objs.add(o.getDelivery());
		
		return objs;
	}

	@Override
	public Object catchObject(Map<String, Object> map) {
		Zone ret = new Zone();
		ret.setZoneCode((Integer) map.get(dbColumns.get("zoneId")));
		ret.setCountry((String) map.get(dbColumns.get("country")));
		ret.setProvince((String) map.get(dbColumns.get("province")));
		ret.setCity((String) map.get(dbColumns.get("city")));
		ret.setDelivery((String) map.get(dbColumns.get("delivery")));
		return ret;
	}
	
	@Override
	public void insert(Zone o) {
		String sql = DBSimpleSQL.insert(dbTable, dbColumns.size());
		this.getJdbcTemplate().update(sql, this.pushObject(o).toArray());
	}

	@Override
	public void update(Zone o) {
		ColumnLimit limit = new ColumnLimit();
		limit.add(dbColumns.get("zoneId") + " = ");
		String sql = DBSimpleSQL.update(dbTable, dbColumns, limit);
		List<Object> objs = this.pushObject(o);
		objs.add(o.getZoneCode());
		this.getJdbcTemplate().update(sql, objs.toArray());
	}

	@Override
	public void delete(int zoneId) {
		ColumnLimit limit = new ColumnLimit();
		limit.add(dbColumns.get("zoneId") + " = ");
		String sql = DBSimpleSQL.del(dbTable, limit);
		this.getJdbcTemplate().update(sql, zoneId);
	}

	@Override
	public List<Zone> query(Zone o, int page, int size) {
		List<Zone> ret = new ArrayList<Zone>();
		String sql = DBSimpleSQL.selectAll(dbTable, this.condition(o), page, size);
		List<Map<String, Object>> maps = this.getJdbcTemplate().queryForList(sql, this.statement(o));
		for (Map<String, Object> map : maps) {
			ret.add((Zone) this.catchObject(map));
		}
		return ret;
	}

	@Override
	public int count(Zone o) {
		String sql = DBSimpleSQL.count(dbTable, this.condition(o));
		return this.getJdbcTemplate().queryForObject(sql, this.statement(o), Integer.class);
	}
	
	@Override
	public List<String> queryCountryByGroup() {
		List<String> ret = new ArrayList<String>();
		String sql = "select COUNTRY from t_zone GROUP BY COUNTRY";
		List<Map<String, Object>> maps =  this.getJdbcTemplate().queryForList(sql);
		for (Map<String, Object> map : maps) {
			ret.add((String) map.get(dbColumns.get("country")));
		}
		return ret;
	}
	
	@Override
	public List<String> queryProvinceByGroup(String country) {
		List<String> ret = new ArrayList<String>();
		String sql = "select PROVINCE from t_zone where COUNTRY='" + country + "' GROUP BY PROVINCE";
		List<Map<String, Object>> maps =  this.getJdbcTemplate().queryForList(sql);
		for (Map<String, Object> map : maps) {
			ret.add((String) map.get(dbColumns.get("province")));
		}
		return ret;
	}
	
	@Override
	public List<String> queryCityByGroup(String province) {
		List<String> ret = new ArrayList<String>();
		String sql = "select CITY from t_zone where PROVINCE='" + province + "' GROUP BY CITY";
		List<Map<String, Object>> maps =  this.getJdbcTemplate().queryForList(sql);
		for (Map<String, Object> map : maps) {
			ret.add((String) map.get(dbColumns.get("city")));
		}
		return ret;
	}
	
}
